from school_models import Student,StudentGrade,SexChoices,CourseGrade
from bson.objectid import ObjectId
from mongoengine.queryset.visitor import Q

class LearnMongoEngine(object):
    # 添加数据
    def add_one(self):
        # 添加数据
        # stu_obj = Student(stu_no='2002',stu_name='王二小')
        stu_obj = Student(stu_no='2003',stu_name='王二小1',phone_no='13500000000') #在模型中加了手机号的验证
        stu_obj.validate()
        result = stu_obj.save()
        print(result)
        print(result.id)

    def add_one1(self):
        # 使用create方法新增数据
        result = Student.objects.create(stu_no='2004',stu_name='王二小2',phone_no='13500000000')
        print(result)
        print(result.id)

    def add_one2(self):
        grade1 = CourseGrade(course_name='语文',score=100)
        grade2 = CourseGrade(course_name='数学',score=100)
        grade3 = CourseGrade(course_name='英语',score=100)
        grades = [grade1,grade2,grade3]
        stu_obj = Student(stu_no='2005',stu_name='王二小3',phone_no='13500000000')
        stu_obj.grades = grades
        stu_obj.validate()
        result = stu_obj.save()
        print(result)
        print(result.id)

    def add_many(self):
        # 批量添加数据
        # 方式1：传入Document对象列表
        # students = [
        #     Student(stu_name="David", stu_no=28),
        #     Student(stu_name="Eve", stu_no=32)
        # ]
        # Student.objects.insert(students)

        # 方式2：循环 save()（不推荐，仅适用于少量数据）
        student_data = [
            {"stu_name": "Alice1", "stu_no": "2511"},
            {"stu_name": "Bob1", "stu_no": "301"},
            {"stu_name": "Charlie1", "stu_no": "351"}
        ]
        for data in student_data:
            Student(**data).save()  # 逐条插入，性能较低

        print("批量插入完成")


    def update_one(self):
        # 数据的修改
        queryset = Student.objects.filter(stu_no=2002)
        # result = queryset.update_one(stu_name='王二大',phone_no='13400000000')
        result = queryset.update_one(stu_name='王二大',unset__phone_no=True)
        print(result)

    def update_one1(self):
        # 数据的修改--save()
        stu_obj = Student.objects.filter(stu_no=2002).first()
        if stu_obj:
            stu_obj.stu_name = '王三'
            result = stu_obj.save()
            print(result)

    def update_many(self):
        # 9岁的学生年龄+1
        queryset = Student.objects.filter(age=10)
        for item in queryset:
            print(item.age)
        queryset.update(inc__age=1)
        print('---------------------')
        queryset = Student.objects.filter(age=11)
        for item in queryset:
            print(item.age)


    def delete_data(self):
        # 删除数据
        # 删除学号>2000的学生信息
        # result = Student.objects.delete(stu_no__gt=2000)
        queryset = Student.objects(stu_no__gt=2000)
        print('学生人数',queryset.count())
        result = queryset.delete()
        print('删除的结果',result)

    def get_one_student(self):
        # 查询一个学生信息
        result = Student.objects.first()
        print(result)
        print(result.stu_name)


    def get_student_by_pk(self,pk):
        # 根据学生的ID来查询--只能匹配一个  如果匹配两个会报错
        result = Student.objects.get(id=pk)
        # 这两个效果一样  上面是mongoengine做了转化，下面的加上也可以
        # object_id = ObjectId(pk)
        # result = Student.objects.get(id=object_id)

        # result = Student.objects.get(age=12) #会报错  因为匹配了一个以上的数据
        print(result)


    def get_students_01(self):
        # 查询所有的文档
        # queryset = Student.objects()
        # queryset = Student.objects.all()
        # 12以上的学生信息
        # queryset = Student.objects(age__gt=12)
        queryset = Student.objects.filter(age__gt=12)
        for item in queryset:
            print(item)

    def get_grade_01(self):
        # 查询60分以上的学生
        queryset = StudentGrade.objects.filter(grade__score__gte=60)
        for item in queryset:
            print(item.stu_name,item.grade.score)

    def get_students_02(self):
        # 查询所有“李”姓的学生
        queryset = Student.objects.filter(stu_name__startswith='李')
        for item in queryset:
            print(item)


    # 多个条件组合查询
    def get_students_03(self):
        # 查询年龄在9~12之间（含）的学生信息
        # queryset = Student.objects.filter(age__gte=9,age__lte=12)
        queryset = Student.objects.filter(Q(age__gte=9) & Q(age__lte=12))
        for item in queryset:
            print(item)

    def get_students_04(self):
        # 查询所有12岁以上的男生和9岁以下的女生
        # queryset = Student.objects.filter(age__gte=9,age__lte=12)
        queryset = Student.objects.filter(Q(age__gt=12,sex=SexChoices.MEN) | Q(age__lt=9,sex=SexChoices.WOMEN))
        for item in queryset:
            print(item)
        print("查询学生的人数和age的总和：",queryset.count(),queryset.sum("age"))

    # 查询数据---统计、排序和分页
    # 统计
    def get_grade_05(self):
        # 统计语文成绩的最高分/最低分/平均分
        queryset = StudentGrade.objects.filter(grade__course_name='语文')
        avg_score = queryset.average('grade.score')
        print('全年级语文成绩平均分',avg_score)
        # 李明的总分
        queryset = StudentGrade.objects.filter(stu_name='李明')
        sum_score = queryset.sum('grade.score')
        print("李明的总分",sum_score)

    # 排序
    def get_grade_06(self):
        queryset = StudentGrade.objects.all()
        # queryset = queryset.order_by('age') #正序
        queryset = queryset.order_by('-age') #倒序
        queryset = queryset.order_by('-age','grade.score')
        for item in queryset:
            print(item.age,item.grade.score)

    # 分页
    # 方式一：切片方式 Student.objects.all()[10:15]
    # 方式二：skip().limit()   Student.objects.skip(10).limit(5)
    def paginate(self,page=1,page_size=5):
        start = (page - 1) * page_size
        end = page * page_size
        queryset = Student.objects.all()[start:end]
        for item in queryset:
            print(item)

        print("-----------------------")
        # skip limit
        queryset = Student.objects().skip(start).limit(page_size)
        for item in queryset:
            print(item)




def main():
    obj = LearnMongoEngine()
    # obj.add_one()
    # obj.add_one1()
    # obj.add_one2()
    # obj.add_many()
    # obj.update_one()
    # obj.update_one1()
    # obj.update_many()
    # obj.delete_data()
    # obj.get_one_student()
    # obj.get_student_by_pk('686e108c741f00009300090f')
    # ID不存在会报错
    # obj.get_student_by_pk('686e108c741f00009300090e')
    # obj.get_students_01()
    # obj.get_grade_01()
    # obj.get_students_02()
    # obj.get_students_03()
    # obj.get_students_04()
    # obj.get_grade_05()
    # obj.get_grade_06()
    obj.paginate()



if __name__ == '__main__':
    main()